﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Shared.Model;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;

namespace Shared.Method
{
    public partial class DBAccess
    {
        //存储特殊需要的一些方法函数

        /// <summary>
        /// 获得最小顺序的流程ID
        /// </summary>
        /// <returns></returns>
        public static FLOW GetFlowIdFromMinSort(long flowId)
        {
            try
            {
                //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName("FLOW")));

                SQLServerContext context = GetSqlContext("FLOW");
                string sql = string.Format("select top 1 * from flow where FLOW_GROUP_ID =(select FLOW_GROUP_ID from FLOW where ID={0}) order by SORT", flowId);
                var listFlow = context.GetTableList<FLOW>(sql, null);
                PushContext(context);
                if (listFlow == null || listFlow.Count == 0)
                {
                    return null;
                }

                return listFlow[0];
                //FLOW flow = context.GetTableList<FLOW>("select * from FLOW where FLOW_GROUP_ID={0} SORT=(select MIN(SORT) from FLOW)", null).FirstOrDefault();
                //int result = context.Session.ExecuteNonQuery("insert into [ERROR] ([ID],[CONTEXT],[METHOD]) values(12333,'TEST2','0')");
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("GetFlowIdFromMinSort()", "查询数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 获得最后一个流程的信息
        /// </summary>
        /// <returns></returns>
        public static FLOW GetFlowIdFromMaxSort(long flowId)
        {
            try
            {
                //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName("FLOW")));
                SQLServerContext context = GetSqlContext("FLOW");
                string sql = string.Format("select top 1 * from flow where FLOW_GROUP_ID =(select FLOW_GROUP_ID from FLOW where ID={0}) order by SORT desc", flowId);
                var listFlow = context.GetTableList<FLOW>(sql, null);
                PushContext(context);
                if (listFlow == null || listFlow.Count == 0)
                {
                    return null;
                }
                return listFlow[0];
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("GetFlowIdFromMaxSort()", "查询数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 获取当前产品的所有过程信息
        /// </summary>
        /// <param name="mac"></param>
        /// <returns></returns>
        public static string GetProcessAll(string mac)
        {
            try
            {
                //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName("PROCESS")));

                SQLServerContext context = GetSqlContext("PROCESS");
                string sql = string.Format("select ID from PROCESS where MAC='{0}' order by START_TIME", mac);
                var listProcess = context.GetColumnList<long>(sql, null);//获取所有的过程信息
                PushContext(context);
                if (listProcess == null || listProcess.Count == 0)
                {
                    return null;
                }
                string result = null;
                for (int i = 0; i < listProcess.Count; i++)//将过程信息写入结果中
                {
                    result += string.Format("{0},", listProcess[i]);
                }
                return result.TrimEnd(',');
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("GetProcessAll()", "查询数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
        }

        //#region SN分配

        ///// <summary>
        ///// 获取最小的产品SN号码
        ///// </summary>
        ///// <param name="lineId"></param>
        ///// <returns></returns>
        //public static string GetMinProductSn(CONDITION_SN conditionSn)
        //{
        //    try
        //    {
        //        //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName("PRODUCT_SN")));

        //        SQLServerContext context = GetSqlContext("PRODUCT_SN");
        //        string sql = "Proc_GetMinProductSn";//存储过程名称

        //        SqlParameter[] paras = new SqlParameter[4];
        //        paras[0] = new SqlParameter("@line_id", conditionSn.LINE_ID);
        //        paras[1] = new SqlParameter("@device_type", conditionSn.TYPE);
        //        paras[2] = new SqlParameter("@province_code", conditionSn.PROVINCE);
        //        paras[3] = new SqlParameter("@min_sn", SqlDbType.BigInt);
        //        paras[3].Direction = ParameterDirection.Output;
        //        context.ExecuteNonQuery(sql, paras, CommandType.StoredProcedure);
        //        return paras[3].Value.ToString();
        //    }
        //    catch (Exception ex)
        //    {
        //        LogHelper.WriteErrLogTxt("GetMinProductSn()", "查询数据错误,", ex.ToString());
        //        throw new Exception(ex.Message);
        //    }
        //}
        ///// <summary>
        ///// 获取和分配最小的SN号码
        ///// </summary>
        ///// <param name="lineId"></param>
        ///// <returns></returns>
        //public static string GetMinBoxSn(CONDITION_SN conditionSn)
        //{
        //    try
        //    {
        //        //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName("PRODUCT_BOX_SN")));
        //        SQLServerContext context = GetSqlContext("PRODUCT_BOX_SN");
        //        string sql = "Proc_GetMinProductBoxSn";//存储过程名称

        //        SqlParameter[] paras = new SqlParameter[4];
        //        paras[0] = new SqlParameter("@line_id", conditionSn.LINE_ID);
        //        paras[1] = new SqlParameter("@device_type", conditionSn.TYPE);
        //        paras[2] = new SqlParameter("@province_code", conditionSn.PROVINCE);

        //        paras[3] = new SqlParameter("@min_sn", SqlDbType.BigInt);
        //        paras[3].Direction = ParameterDirection.Output;
        //        context.ExecuteNonQuery(sql, paras, CommandType.StoredProcedure);
        //        return paras[3].Value.ToString();
        //    }
        //    catch (Exception ex)
        //    {
        //        LogHelper.WriteErrLogTxt("GetMinBoxSn()", "查询数据错误,", ex.ToString());
        //        throw new Exception(ex.Message);
        //    }
        //}
        ///// <summary>
        ///// 获取最小的SN号码
        ///// </summary>
        ///// <param name="lineId"></param>
        ///// <returns></returns>
        //public static string GetPackingProductSn(CONDITION_SN conditionSn)
        //{
        //    try
        //    {
        //        //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName("PRODUCT")));
        //        SQLServerContext context = GetSqlContext("PRODUCT");

        //        string sql = string.Format("select MIN(SN) from PRODUCT where LINE_ID={0} ",conditionSn.LINE_ID);
        //        if (string.IsNullOrEmpty(conditionSn.TYPE)==false) sql = string.Format("{0} and TYPE='{1}'", sql,conditionSn.TYPE);
        //        if (string.IsNullOrEmpty(conditionSn.PROVINCE) == false) sql = string.Format("{0} and PROVINCE='{1}'", sql, conditionSn.PROVINCE);
        //        sql = string.Format("{0} and  SN<>'' and SN is not null and (BOX_SN is null or BOX_SN='') ", sql);

        //        string productSn = context.GetFirstCell<string>(sql, null);
        //        return productSn;
        //    }
        //    catch (Exception ex)
        //    {
        //        LogHelper.WriteErrLogTxt("GetPackingProductSn()", "查询数据错误,", ex.ToString());
        //        throw new Exception(ex.Message);
        //    }
        //}
        ///// <summary>
        ///// 分配SN
        ///// </summary>
        ///// <param name="conditionSn"></param>
        ///// <returns></returns>
        //public static bool DistributeBoxProSn(CONDITION_SN conditionSn)
        //{
        //    try
        //    {
        //        //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName("PRODUCT_SN")));
        //        SQLServerContext context = GetSqlContext("PRODUCT_SN");
        //        string sql = "Proc_DistributeSN";//存储过程名称
        //        SqlParameter[] paras = new SqlParameter[6];
        //        paras[0] = new SqlParameter("@line_id", conditionSn.LINE_ID);

        //        paras[1] = new SqlParameter("@device_type", SqlDbType.VarChar,100);
        //        paras[1].SqlValue = conditionSn.TYPE;

        //        paras[2] = new SqlParameter("@province_code", SqlDbType.VarChar,100);
        //        paras[2].SqlValue = conditionSn.PROVINCE;

        //        paras[3] = new SqlParameter("@block_num", conditionSn.BLOCK_NUM);

        //        paras[4] = new SqlParameter("@proc_msg",SqlDbType.VarChar,100);
        //        paras[4].Direction = ParameterDirection.Output;

        //        paras[5] = new SqlParameter("@returnValue",SqlDbType.Int);
        //        paras[5].Direction = ParameterDirection.ReturnValue;

        //        context.ExecuteNonQuery(sql, paras, CommandType.StoredProcedure);

        //        conditionSn.MSG = paras[4].Value.ToString();
        //        if ((int)paras[5].Value == -1)//如果不能得到相应的数据返回空
        //        {
        //            return false;
        //        }

        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        LogHelper.WriteErrLogTxt("DistributeBoxProSn()", "执行存储过程错误,", ex.ToString());
        //        throw new Exception(ex.Message);
        //    }
        //}

        //#endregion

        #region 获取YCD_MAC
        public static string GetYcdMac(string ponType)
        {
            #region change new method

            string sql = $@"
DECLARE	@return_value int,
        @ycd_mac varchar(20),
		@message varchar(200)

EXEC @return_value = [dbo].[Proc_GetYcdMac]

        @pon_type = N'{ponType}',
		@ycd_mac = @ycd_mac OUTPUT,
		@message = @message OUTPUT

SELECT  @ycd_mac as N'@ycd_mac'
";

            try
            {
                string ycdMac = ExecuteGetReturn<string>(sql, "YCD_DEVICE", null);
                return ycdMac;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("GetMinBoxSn()", "查询数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
            #endregion
#if false
            try
            {
                //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName("PRODUCT_BOX_SN")));
                SQLServerContext context = GetSqlContext("YCD_DEVICE",true);
                //SQLServerContext context = GetSqlContext("YCD_DEVICE");
                string sql = "Proc_GetYcdMac";//存储过程名称

                SqlParameter[] paras = new SqlParameter[4];
                paras[0] = new SqlParameter("@pon_type", ponType);
                paras[1] = new SqlParameter("@ycd_mac", SqlDbType.VarChar, 100)
                {
                    Direction = ParameterDirection.Output
                };
                paras[2] = new SqlParameter("@message", SqlDbType.VarChar, 100)
                {
                    Direction = ParameterDirection.Output
                };

                paras[3] = new SqlParameter("@return_value", SqlDbType.Int)
                {
                    Direction = ParameterDirection.ReturnValue
                };

                context.ExecuteNonQuery(sql, paras, CommandType.StoredProcedure);
                PushContext(context);
                if ((int)paras[3].Value == -1) return null;
                return paras[1].Value.ToString();
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("GetMinBoxSn()", "查询数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
#endif
        }

        #endregion
    }





    public partial class DBAccess
    {
        private static string _connStr = null;
        private static object _lockObj = new object();

        /// <summary>
        /// 保存连接的对象类
        /// </summary>
        private static List<SQLServerContext> _listSqlContext = new List<SQLServerContext>();

        private static Stack<SQLServerContext>[] _stackContext = new Stack<SQLServerContext>[6]
        {
            new Stack<SQLServerContext>(),//trctl
            new Stack<SQLServerContext>(),//trctltran
            new Stack<SQLServerContext>(),//modem
            new Stack<SQLServerContext>(),//modemtrctl
            new Stack<SQLServerContext>(),//trtest
            new Stack<SQLServerContext>()//trtestctl
        };


        private static int GetStackIndex(string dbName, bool transcationType)
        {
            int indexStack = -1;
            if (transcationType == false)
            {
                switch (dbName)
                {
                    case "TRCTL":
                        indexStack = 0;
                        break;
                    case "Modem":
                        indexStack = 2;
                        break;
                    case "TRTEST":
                        indexStack = 4;
                        break;
                }
            }
            else
            {
                switch (dbName)
                {
                    case "TRCTL":
                        indexStack = 1;
                        break;
                    case "Modem":
                        indexStack = 3;
                        break;
                    case "TRTEST":
                        indexStack = 5;
                        break;
                }
            }

            return indexStack;
        }

        private static void PushContext(SQLServerContext context)
        {
            if (context == null) return;

            int indexStack = GetStackIndex(context.DatabaseName, context.TranscationType);

            
            lock (_stackContext)
                _stackContext[indexStack].Push(context);

        }

        /// <summary>
        /// 获取连接对象
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static SQLServerContext GetSqlContext(string tableName, bool transcationType = false)
        {
            string dbName = GetDbName(tableName);
            string connStr = GetConnectionStr(dbName);
            SQLServerContext context = null;

            //LogHelper.WriteDebugLogTxt("当前连接对象数量", _listSqlContext.Count.ToString());
            //LogHelper.WriteDebugLogTxt("count of sql connect", _listSqlContext.Count.ToString());

            #region using stack save context

            int indexStack = GetStackIndex(dbName, transcationType);

            if (indexStack >= 0)
            {
                lock (_stackContext)
                    if (_stackContext[indexStack].Count > 0)
                        context = _stackContext[indexStack].Pop();

                if (context == null) context = new SQLServerContext(connStr, transcationType);
                return context;
            }
            else
            {
                return null;
            }

            #endregion
#if false
            if (_listSqlContext.Count == 0)
            {
                context = new SQLServerContext(connStr, transcationType);
                _listSqlContext.Add(context);
                return context;
            }

            lock (_lockObj)
            {
                context = (from n in _listSqlContext
                           where n.IsUse == false && n.DatabaseName == dbName && n.TranscationType == transcationType
                           select n).FirstOrDefault();

                if (context != null)
                    context.IsUse = true;
            }

            if (context == null)
            {
                context = new SQLServerContext(connStr, transcationType);
                _listSqlContext.Add(context);
            }

            return context;
#endif
        }

        /// <summary>
        /// 从配置文件中获取数据库的名称
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static string GetDbName(string tableName)
        {
            return ConfigDatabase.GetXmlConfigTable(tableName);
        }

        /// <summary>
        /// 获得数据库连接字符串
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private static string GetConnectionStr(string dbName)
        {
            _connStr = Encrypt.Decrypt3DES(ConfigDatabase.GetXmlConfigConnStr(dbName));

            if (string.IsNullOrEmpty(_connStr))
            {
                throw new Exception("获取数据库连接context失败");
            }
            return _connStr;
        }

        /// <summary>
        /// 获得当前ListWhere或者ListUpdate的对象的属性
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="value"></param>
        /// <returns>返回PropertyInfo的列表</returns>
        private static List<PropertyInfo> GetPropertys<T>(T value, bool isWhere = true) where T : TableBase
        {
            //直接利用基类得到listStr
            List<string> ListPropertyStr = null;
            if (isWhere)
                ListPropertyStr = value.GetListWhere();
            else
                ListPropertyStr = value.GetListUpdate();

            //如果不能获得List的话，返回空
            if (ListPropertyStr == null || ListPropertyStr.Count == 0)
            {
                return null;
            }

            //使用属性方法获得变量值，原来的方法是先获得所有属性，接着判断属性是否在listStr中
            List<PropertyInfo> listProperty = new List<PropertyInfo>();//属性
            for (int i = 0; i < ListPropertyStr.Count; i++)
            {
                listProperty.Add(typeof(T).GetProperty(ListPropertyStr[i]));
            }
            return listProperty;
        }

        //直接执行SQL的接口
        /// <summary>
        /// 执行SQL语句，返回修改数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery<T>(string sql, SqlParameter[] paras = null)
        {
            try
            {
                SQLServerContext context = GetSqlContext(typeof(T).Name, true);
                //SQLServerContext context = GetSqlContext(typeof(T).Name);
                var result = context.ExecuteNonQuery(sql, paras);
                PushContext(context);
                return result;
            }
            catch (Exception ex)//if some problem has happend in execute sql , the connection would been abandon
            {
                LogHelper.WriteErrLogTxt("ExecuteNonQuery<T>() ", sql, ex.ToString());
                return 0;
            }
        }

        public static T ExecuteGetReturn<T>(string sql, string tableName, SqlParameter[] paras = null)
        {
            try
            {
                SQLServerContext context = GetSqlContext(tableName, true);
                var result = context.GetFirstCell<T>(sql, paras);
                PushContext(context);
                return result;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("ExecuteGetReturn<T>() ",sql, ex.ToString());
                return default(T);
            }
        }

        /// <summary>
        /// 执行SQL语句获得表数据列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static List<T> ExecuteGetTable<T>(string sql, SqlParameter[] paras = null) where T : TableBase, new()
        {
            try
            {
                SQLServerContext context = GetSqlContext(typeof(T).Name);
                var result = context.GetTableList<T>(sql, paras);
                PushContext(context);
                return result;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("ExecuteGetTable<T>(),Table:" + typeof(T).Name, sql, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 通用型查询语句，返回结果
        /// </summary>
        /// <typeparam name="T">数据库表类</typeparam>
        /// <param name="value">实例</param>
        /// <returns></returns>
        public static List<T> SelectData<T>(T value) where T : TableBase, new()
        {
            //查询语句
            try
            {
                List<PropertyInfo> listWhereP = GetPropertys(value);

                //如果没有相应的属性的话，返回空
                if (listWhereP == null || listWhereP.Count == 0)
                {
                    return SelectAll(value);
                }

                List<T> selectRes = new List<T>();//返回结果

                //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName(typeof(T).Name)));
                SQLServerContext context = GetSqlContext(typeof(T).Name);
                //查询SQL语句与变量
                SqlParameter[] paras = new SqlParameter[listWhereP.Count];//SQL变量保存的地方
                string sql = "select  *  from " + typeof(T).Name + "  where  ";

                //add_18.3.10 增加额外的SQL语句
                if (string.IsNullOrEmpty(value.GetStrWhereSQL()) == false)
                {
                    sql += value.GetStrWhereSQL() + " AND ";
                }

                for (int i = 0; i < listWhereP.Count - 1; i++)
                {
                    sql += listWhereP[i].Name + " = " + " @P" + i + " AND ";
                    paras[i] = SQLServerContext.CreateParameter("@P" + i, listWhereP[i].GetValue(value, null)); /*GetGetMethod(true).Invoke(value, null));*/
                }
                sql += listWhereP[listWhereP.Count - 1].Name + " = " + " @P" + (listWhereP.Count - 1);
                paras[listWhereP.Count - 1] = SQLServerContext.CreateParameter("@P" + (listWhereP.Count - 1), listWhereP[listWhereP.Count - 1].GetValue(value, null));

                //查询SQL语句的执行之处
                selectRes = context.GetTableList<T>(sql, paras);
                PushContext(context);
                //return result;
                return selectRes;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("SelectData<T>(),Table: " + typeof(T).Name, "查询数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 返回单个表中指定条件的数据条数目
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int SelectCount<T>(T value) where T : TableBase
        {
            #region new method
            string sql = ObjectToSQL.ObjectToSelectCountSql(value);
            try
            {
                SQLServerContext context = GetSqlContext(typeof(T).Name);
                int count = context.GetFirstCell<int>(sql, null);
                PushContext(context);
                return count;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("SelectCount<T>(),Table: " + typeof(T).Name, sql, ex.ToString());
                throw new Exception(ex.Message);
            }
            #endregion

#if false
            List<PropertyInfo> listWhereP = GetPropertys(value);

            //如果没有相应的属性的话，返回空
            if (listWhereP == null || listWhereP.Count == 0)
            {
                return SelectCountAll(value);
            }

            int selectRes = 0;//返回结果

            //查询语句
            try
            {
                //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName(typeof(T).Name)));
                SQLServerContext context = GetSqlContext(typeof(T).Name,true);
                //查询SQL语句与变量
                SqlParameter[] paras = new SqlParameter[listWhereP.Count];//SQL变量保存的地方
                string sql = "select  count(*) AS C from " + typeof(T).Name + "  where  ";

                //add_18.3.10 增加额外的SQL语句
                if (string.IsNullOrEmpty(value.GetStrWhereSQL()) == false)
                {
                    sql += value.GetStrWhereSQL() + " AND ";
                }

                for (int i = 0; i < listWhereP.Count - 1; i++)
                {
                    sql += listWhereP[i].Name + " = " + " @P" + i + " AND ";
                    paras[i] = SQLServerContext.CreateParameter("@P" + i, listWhereP[i].GetValue(value, null));
                }
                sql += listWhereP[listWhereP.Count - 1].Name + " = " + " @P" + (listWhereP.Count - 1);
                paras[listWhereP.Count - 1] = SQLServerContext.CreateParameter("@P" + (listWhereP.Count - 1), listWhereP[listWhereP.Count - 1].GetValue(value, null));

                //查询SQL语句的执行之处
                selectRes = context.GetFirstCell<int>(sql, paras);
                PushContext(context);
                //return result;
                return selectRes;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("SelectCount<T>(),Table: " + typeof(T).Name, "查询数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
#endif
        }

        /// <summary>
        /// 通用型查询全表数据，返回开头的5000条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="value"></param>
        /// <returns></returns>
        public static List<T> SelectAll<T>(T value, int num = 5000) where T : TableBase, new()
        {
            try
            {
                SQLServerContext context = GetSqlContext(typeof(T).Name);
                List<T> listRes = new List<T>();

                string sql = string.Empty;
                if (value != null && string.IsNullOrEmpty(value.GetStrWhereSQL()) == false)
                {
                    sql = string.Format("select * from {0} where {1}", typeof(T).Name, value.GetStrWhereSQL());
                }
                else
                {
                    sql = string.Format("select TOP {0} * from {1}", num, typeof(T).Name);//查询前5000个数据
                }

                var result = context.GetTableList<T>(sql, null);

                PushContext(context);
                return result;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("SelectAll<T>(),Table: " + typeof(T).Name, "查询数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 查询表中的数据总条数
        /// </summary>
        /// <typeparam name="T">表类</typeparam>
        /// <returns>数据条数</returns>
        public static int SelectCountAll<T>(T value) where T : TableBase
        {
            try
            {
                //MsSqlContext context = new MsSqlContext(GetConnectionStr(IsDataBaseTRTEST(typeof(T).Name)));
                //IQuery<T> query = context.Query<T>();
                //int selectRes = query.Count();
                //context.Dispose();
                //return selectRes;

                //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName(typeof(T).Name)));
                SQLServerContext context = GetSqlContext(typeof(T).Name);
                string sql = string.Format("select count(*) from {0}", typeof(T).Name);

                if (value != null && string.IsNullOrEmpty(value.GetStrWhereSQL()) == false)
                {
                    sql += string.Format(" WHERE {0}", value.GetStrWhereSQL());
                }

                int selectRes = context.GetFirstCell<int>(sql, null);
                PushContext(context);
                //return result;
                return selectRes;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("SelectCountAll<T>(),Table: " + typeof(T).Name, "查询数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
        }

        public static string SelectDataJson(string sql, string tableName)
        {
            try
            {
                SQLServerContext context = GetSqlContext(tableName);
                string result = context.GetDatatableJson(sql, null);
                PushContext(context);
                return result;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("SelectDataJson,Table: " + tableName, sql, ex.ToString());
                throw new Exception(ex.Message);
            }

        }

        /// <summary>
        /// 通用型删除方法
        /// </summary>
        /// <typeparam name="T">表类</typeparam>
        /// <param name="value">表类实体</param>
        /// <returns></returns>
        public static int DeleteData<T>(T value) where T : TableBase
        {
            string sql = ObjectToSQL.ObjectToDeleteSql(value);
            try
            {
                SQLServerContext context = GetSqlContext(typeof(T).Name, true);
                //SQLServerContext context = GetSqlContext(typeof(T).Name);

                var result = context.ExecuteNonQuery(sql, null);
                PushContext(context);
                return result;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("Delete<T>(),Table: " + typeof(T).Name, sql, ex.ToString());
                throw new Exception(ex.Message);
            }

#if false

            List<PropertyInfo> listWhereP = GetPropertys(value);

            //如果没有相应的属性的话，返回空
            if ((listWhereP == null || listWhereP.Count == 0)
                && (string.IsNullOrEmpty(value.GetStrWhereSQL())))
            {
                return 0;
            }

            //查询语句
            try
            {
                //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName(typeof(T).Name)));
                SQLServerContext context = GetSqlContext(typeof(T).Name);
                //查询SQL语句与变量
                SqlParameter[] paras = null;
                string sql = "delete  from " + typeof(T).Name + "  where  ";

                if (listWhereP != null && listWhereP.Count > 0)
                {
                    paras = new SqlParameter[listWhereP.Count];//SQL变量保存的地方
                    for (int i = 0; i < listWhereP.Count - 1; i++)
                    {
                        sql += listWhereP[i].Name + " = " + " @P" + i + " AND ";
                        paras[i] = SQLServerContext.CreateParameter("@P" + i, listWhereP[i].GetValue(value, null));
                    }
                    sql += listWhereP[listWhereP.Count - 1].Name + " = " + " @P" + (listWhereP.Count - 1);
                    paras[listWhereP.Count - 1] = SQLServerContext.CreateParameter("@P" + (listWhereP.Count - 1), listWhereP[listWhereP.Count - 1].GetValue(value, null));
                }
                //add_18.3.10 增加额外的SQL语句
                if (string.IsNullOrEmpty(value.GetStrWhereSQL()) == false)
                {
                    if (listWhereP != null && listWhereP.Count > 0)
                    { sql += " AND "; }//有两者条件的时候
                    sql += value.GetStrWhereSQL();
                }

                //test
                sql = ObjectToDeleteSqll(value);
                paras = null;

                //查询SQL语句的执行之处
                return context.ExecuteNonQuery(sql, paras);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("Delete<T>(),Table: " + typeof(T).Name, "删除数据错误,", ex.ToString());

                throw new Exception(ex.Message);
            }
#endif
        }

        /// <summary>
        /// 通用型数据更新方
        /// </summary>
        /// <typeparam name="T">表类</typeparam>
        /// <param name="value">表类实体</param>
        /// <returns></returns>
        public static int UpdateData<T>(T value) where T : TableBase
        {
            string sql = ObjectToSQL.ObjectToUpdateSql(value);
            try
            {
                SQLServerContext context = GetSqlContext(typeof(T).Name, true);
                //SQLServerContext context = GetSqlContext(typeof(T).Name);

                //查询SQL语句的执行之处
                var result = context.ExecuteNonQuery(sql, null);
                PushContext(context);
                return result;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("Update<T>(),Table: " + typeof(T).Name, sql, ex.ToString());
                throw new Exception(ex.Message);
            }
#if false
            List<PropertyInfo> listWhereP = GetPropertys(value);
            List<PropertyInfo> listUpdateP = GetPropertys(value, false);

            //如果没有相应的属性的话，返回空
            if (listUpdateP == null || listUpdateP.Count == 0)
            {
                return 0;
            }

            if ((listWhereP == null || listWhereP.Count == 0)
                && (string.IsNullOrEmpty(value.GetStrWhereSQL())))
            {
                return 0;
            }

            //更新语句
            try
            {
                SQLServerContext context = GetSqlContext(typeof(T).Name);
                //查询SQL语句与变量

                SqlParameter[] paras = null;
                if (listWhereP != null && listWhereP.Count > 0)
                {
                    paras = new SqlParameter[listWhereP.Count + listUpdateP.Count];//SQL变量保存的地方
                }
                else
                {
                    paras = new SqlParameter[listUpdateP.Count];//SQL变量保存的地方
                }

                //update
                string sql = "update " + typeof(T).Name + " set ";
                for (int i = 0; i < listUpdateP.Count - 1; i++)
                {
                    sql += listUpdateP[i].Name + " = " + " @U" + i + ", ";
                    paras[i] = SQLServerContext.CreateParameter("@U" + i, listUpdateP[i].GetValue(value, null));
                }

                sql += listUpdateP[listUpdateP.Count - 1].Name + " = " + " @U" + (listUpdateP.Count - 1) + " where ";
                paras[listUpdateP.Count - 1] = SQLServerContext.CreateParameter("@U" + (listUpdateP.Count - 1), listUpdateP[listUpdateP.Count - 1].GetValue(value, null));

                //where
                if (listWhereP != null && listWhereP.Count > 0)
                {
                    for (int i = 0; i < listWhereP.Count - 1; i++)
                    {
                        sql += listWhereP[i].Name + " = " + " @W" + i + " AND ";
                        paras[i + listUpdateP.Count] = SQLServerContext.CreateParameter("@W" + i, listWhereP[i].GetValue(value, null));
                    }
                    sql += listWhereP[listWhereP.Count - 1].Name + " = " + " @W" + (listWhereP.Count - 1);
                    paras[listWhereP.Count - 1 + listUpdateP.Count] = SQLServerContext.CreateParameter("@W" + (listWhereP.Count - 1), listWhereP[listWhereP.Count - 1].GetValue(value, null));
                }

                //add_18.3.10 增加额外的SQL语句
                if (string.IsNullOrEmpty(value.GetStrWhereSQL()) == false)
                {
                    if (listWhereP != null && listWhereP.Count > 0)
                    { sql += " AND "; }
                    sql += value.GetStrWhereSQL();
                }

                //test
                sql = ObjectToUpdateSqll(value);
                paras = null;

                //查询SQL语句的执行之处
                return context.ExecuteNonQuery(sql, paras);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("Update<T>(),Table: " + typeof(T).Name, "更新数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
#endif
        }

        //2017.10.28_ADD
#if false
            //old insert no use
        public static int InsertData<T>(T value) where T : TableBase
        {
            List<PropertyInfo> listUpdateP = GetPropertys(value, false);

            //如果没有相应的属性的话，返回空
            if (listUpdateP == null || listUpdateP.Count == 0)
            {
                return 0;
            }

            //插入语句
            try
            {
                //使用新的方法执行插入数据的操作
                //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName(typeof(T).Name)));

                SQLServerContext context = GetSqlContext(typeof(T).Name,true);
                SqlParameter[] paras = new SqlParameter[listUpdateP.Count];//给参数赋值

                string columns = string.Empty;//表项目
                string values = string.Empty;//表值
                for (int i = 0; i < listUpdateP.Count; i++)
                {
                    columns += string.Format("{0},", listUpdateP[i].Name);
                    values += string.Format("@U{0},", i);
                    paras[i] = SQLServerContext.CreateParameter(string.Format("@U{0}", i), listUpdateP[i].GetValue(value, null));
                }

                string sql = string.Format("insert into {0} ( {1} ) values ( {2} ) ", typeof(T).Name, columns.TrimEnd(','), values.TrimEnd(','));

                return context.ExecuteNonQuery(sql, paras);
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("Insert<T>(),Table: " + typeof(T).Name, "写入数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
        }
#endif


        //返回自增长ID的插入方法
        public static long InsertDataIdentify<T>(T value) where T : TableBase
        {

            #region new method

            string sql = ObjectToSQL.ObjectToInsertSql(value);
            try
            {
                SQLServerContext context = GetSqlContext(typeof(T).Name, true);

                var result = (long)context.GetFirstCell<decimal>(sql, null);
                PushContext(context);
                return result;
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("InsertDataIdentify<T>(),Table: " + typeof(T).Name, sql, ex.ToString());
                throw new Exception(ex.Message);
            }

            #endregion
#if false
            List<PropertyInfo> listUpdateP = GetPropertys(value, false);

            //如果没有相应的属性的话，返回空
            if (listUpdateP == null || listUpdateP.Count == 0)
            {
                return 0;
            }

            //插入语句
            try
            {
                //使用新的方法执行插入数据的操作
                //SQLServerContext context = new SQLServerContext(GetConnectionStr(GetDbName(typeof(T).Name)));

                SQLServerContext context = GetSqlContext(typeof(T).Name);
                SqlParameter[] sqlParas = new SqlParameter[listUpdateP.Count];//给参数赋值

                string columns = string.Empty;//表项目
                string values = string.Empty;//表值
                for (int i = 0; i < listUpdateP.Count; i++)
                {
                    columns += string.Format("{0},", listUpdateP[i].Name);
                    values += string.Format("@U{0},", i);
                    sqlParas[i] = SQLServerContext.CreateParameter(string.Format("@U{0}", i), listUpdateP[i].GetValue(value, null));
                }

                string sql = string.Format("insert into {0} ( {1} ) values ( {2} ) ", typeof(T).Name, columns.TrimEnd(','), values.TrimEnd(','));
                sql += "  select @@identity";

                //test
                sql = ObjectToInsertSqll(value);
                sqlParas = null;

                return (long)context.GetFirstCell<decimal>(sql, sqlParas);
                //这是一个坑！！！在数字后加了一个M表示为decimal
            }
            catch (Exception ex)
            {
                LogHelper.WriteErrLogTxt("InsertDataIdentify<T>(),Table: " + typeof(T).Name, "写入数据错误,", ex.ToString());
                throw new Exception(ex.Message);
            }
#endif
        }

#if false
        private static string ObjectToInsertSqll<T>(T obj) where T : TableBase
        {
            if (obj == null) return string.Empty;

            PropertyInfo[] infos = typeof(T).GetProperties();

            if (infos == null) return string.Empty;
            List<string> insertList = obj.GetListUpdate();

            if (insertList == null || insertList.Count == 0) return string.Empty;

            StringBuilder sqlCols = new StringBuilder();
            StringBuilder sqlValues = new StringBuilder();

            for (int i = 0; i < infos.Length; i++)
            {
                if (insertList.Contains(infos[i].Name) == false) continue;

                sqlCols.Append($"{infos[i].Name},");
                //sqlCols.Append(",");

                //two single quotation mask means single quotation symbol

                if (infos[i].GetValue(obj) == null)
                {
                    sqlValues.Append("NULL,");
                }
                else if (infos[i].PropertyType == typeof(string))
                {
                    sqlValues.Append($"'{infos[i].GetValue(obj).ToString().Replace("'","''")}',");
                }
                else if (
                    infos[i].PropertyType == typeof(DateTime) ||
                    infos[i].PropertyType == typeof(DateTime?) ||
                    infos[i].PropertyType == typeof(bool) ||
                    infos[i].PropertyType == typeof(bool?))
                {
                    sqlValues.Append($"'{infos[i].GetValue(obj)}',");
                }
                else
                {
                    sqlValues.Append($"{infos[i].GetValue(obj)},");
                }
            }

            sqlCols.Remove(sqlCols.Length - 1, 1);
            sqlValues.Remove(sqlValues.Length - 1, 1);

            string sql = $"INSERT INTO {typeof(T).Name}  ({sqlCols}) VALUES ({sqlValues}) select @@identity";

            return sql;
        }

        private static string ObjectToUpdateSqll<T>(T obj) where T : TableBase
        {
            if (obj == null) return string.Empty;

            List<string> whereList = obj.GetListWhere();
            List<string> updateList = obj.GetListUpdate();

            StringBuilder whereBuf = new StringBuilder();

            PropertyInfo info;

            for (int i = 0; whereList != null && whereList.Count > 0 && i < whereList.Count; i++)
            {
                info = typeof(T).GetProperty(whereList[i]);

                if (info.GetValue(obj) == null)
                {
                    whereBuf.Append($"{info.Name}=NULL ");
                }
                else if (info.PropertyType == typeof(string))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj).ToString().Replace("'", "''")}' ");
                }
                else if (
                    info.PropertyType == typeof(DateTime) ||
                    info.PropertyType == typeof(bool) ||
                    info.PropertyType == typeof(DateTime?) ||
                    info.PropertyType == typeof(bool?))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj)}' ");
                }
                else
                {
                    whereBuf.Append($"{info.Name} = {info.GetValue(obj)} ");
                }

                if (i != whereList.Count - 1)
                {
                    whereBuf.Append("AND ");
                }
            }

            string whereSql = obj.GetStrWhereSQL();

            if (string.IsNullOrEmpty(whereSql) == false)
            {
                if (whereList != null && whereList.Count > 0)
                {
                    whereBuf.Append("AND ");
                }

                whereBuf.Append(whereSql);
            }

            StringBuilder updateBuf = new StringBuilder();

            for (int i = 0; updateList != null && updateList.Count > 0 && i < updateList.Count; i++)
            {
                info = typeof(T).GetProperty(updateList[i]);

                if (info.GetValue(obj) == null)
                {
                    updateBuf.Append($"{info.Name}=NULL");
                }
                else if (info.PropertyType == typeof(string))
                {
                    updateBuf.Append($"{info.Name} = '{info.GetValue(obj).ToString().Replace("'", "''")}' ");
                }
                else if (
                    info.PropertyType == typeof(DateTime) ||
                    info.PropertyType == typeof(bool) ||
                    info.PropertyType == typeof(DateTime?) ||
                    info.PropertyType == typeof(bool?))
                {
                    updateBuf.Append($"{info.Name} = '{info.GetValue(obj)}'");
                }
                else
                {
                    updateBuf.Append($"{info.Name} = {info.GetValue(obj)}");
                }

                if (i != updateList.Count - 1)
                {
                    updateBuf.Append(",");
                }
            }

            string sql = $"UPDATE {typeof(T).Name} SET {updateBuf} WHERE {whereBuf}";

            return sql;
        }

        private static string ObjectToDeleteSqll<T>(T obj) where T : TableBase
        {
            if (obj == null) return string.Empty;

            List<string> whereList = obj.GetListWhere();

            StringBuilder whereBuf = new StringBuilder();


            PropertyInfo info;

            for (int i = 0; whereList != null && whereList.Count > 0 && i < whereList.Count; i++)
            {
                info = typeof(T).GetProperty(whereList[i]);

                if (info.GetValue(obj) == null)
                {
                    whereBuf.Append($"{info.Name}=NULL ");
                }
                else if (info.PropertyType == typeof(string))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj).ToString().Replace("'", "''")}' ");
                }
                else if (
                    info.PropertyType == typeof(DateTime) ||
                    info.PropertyType == typeof(bool) ||
                    info.PropertyType == typeof(DateTime?) ||
                    info.PropertyType == typeof(bool?))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj)}' ");
                }
                else
                {
                    whereBuf.Append($"{info.Name} = {info.GetValue(obj)} ");
                }

                if (i != whereList.Count - 1)
                {
                    whereBuf.Append("AND ");
                }
            }

            string whereSql = obj.GetStrWhereSQL();

            if (string.IsNullOrEmpty(whereSql) == false)
            {
                if (whereList != null && whereList.Count > 0)
                {
                    whereBuf.Append(" AND ");
                }
                whereBuf.Append(whereSql);
            }

            string sql = $"DELETE FROM {typeof(T).Name} WHERE {whereBuf}";
            //LogHelper.WriteDebugLogTxt("DELETE SQL", sql);
            return sql;
        }

        private static string ObjectToSelectCountSqll<T>(T obj) where T : TableBase
        {
            if (obj == null) return string.Empty;

            List<string> whereList = obj.GetListWhere();

            StringBuilder whereBuf = new StringBuilder();

            PropertyInfo info;

            for (int i = 0; whereList != null && whereList.Count > 0 && i < whereList.Count; i++)
            {
                info = typeof(T).GetProperty(whereList[i]);

                if (info.GetValue(obj) == null)
                {
                    whereBuf.Append($"{info.Name}=NULL ");
                }
                else if (info.PropertyType == typeof(string))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj).ToString().Replace("'", "''")}' ");
                }
                else if (
                    info.PropertyType == typeof(DateTime) ||
                    info.PropertyType == typeof(bool) ||
                    info.PropertyType == typeof(DateTime?) ||
                    info.PropertyType == typeof(bool?))
                {
                    whereBuf.Append($"{info.Name} = '{info.GetValue(obj)}' ");
                }
                else
                {
                    whereBuf.Append($"{info.Name} = {info.GetValue(obj)} ");
                }

                if (i != whereList.Count - 1)
                {
                    whereBuf.Append("AND ");
                }
            }

            string whereSql = obj.GetStrWhereSQL();

            if (string.IsNullOrEmpty(whereSql) == false)
            {
                if (whereList != null && whereList.Count > 0)
                {
                    whereBuf.Append(" AND ");
                }
                whereBuf.Append(whereSql);
            }

            string sql = $"SELECT COUNT(*) FROM {typeof(T).Name} WHERE {whereBuf}";
            //LogHelper.WriteDebugLogTxt("DELETE SQL", sql);
            return sql;
        }
#endif
    }
}
